﻿Imports DTO
Imports System.Data.SqlClient
Imports System.Data

Public Class CDocGiaDAO
    ' Load toàn bộ độc giả
    Public Function LoadDanhSachDocGia() As List(Of CDocGiaDTO)
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As SqlCommand = New SqlCommand("uspLayDanhSachDocGia", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim listdocgia As New List(Of CDocGiaDTO)
            Using reader As IDataReader = cm.ExecuteReader(CommandBehavior.CloseConnection)
                While reader.Read()
                    listdocgia.Add(New CDocGiaDTO(reader))
                End While
            End Using
            Return listdocgia
        End Using
    End Function


    ' Lấy giá trị thẻ:
    Public Function LayGiaTriThe() As Integer
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As SqlCommand = New SqlCommand("usp_LayGiaTriThe", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim gtt As Integer = cm.ExecuteScalar()
            Return gtt
        End Using
    End Function


    ' Kiểm tra tuổi của độc giả từ 18 đến 55
    Public Function KiemTraTuoiHopLe(ByVal _docgia As CDocGiaDTO) As Integer
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As SqlCommand = New SqlCommand("usp_KiemTraTuoiHopLe", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim ns As Integer = Year(_docgia.NgaySinh)
            Dim para As New SqlParameter("@namsinh", ns)
            cm.Parameters.Add(para)
            Dim parahl As New SqlParameter("@kthl", 0)
            parahl.Direction = ParameterDirection.Output
            cm.Parameters.Add(parahl)

            cm.ExecuteNonQuery()

            cn.Close()
            Return cm.Parameters("@kthl").Value
        End Using
    End Function


    ' Thêm độc giả
    Public Function ThemDocGia(ByRef _docgia As CDocGiaDTO) As Integer
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As SqlCommand = New SqlCommand("uspThemDocGia", cn)
            cm.CommandType = CommandType.StoredProcedure

            '@hoten,@diachi,@ngaysinh,@email,@ngaylapthe,@ngayhethan,@tongno,@maloaidocgia,@manhanvien
            Dim para As New SqlParameter("@hoten", _docgia.HoTen)
            cm.Parameters.Add(para)
            para = New SqlParameter("@diachi", _docgia.DiaChi)
            cm.Parameters.Add(para)
            para = New SqlParameter("@ngaysinh", _docgia.NgaySinh)
            cm.Parameters.Add(para)
            para = New SqlParameter("@email", _docgia.Email)
            cm.Parameters.Add(para)
            para = New SqlParameter("@ngaylapthe", _docgia.NgayLapThe)
            cm.Parameters.Add(para)
            para = New SqlParameter("@ngayhethan", _docgia.NgayHetHan)
            cm.Parameters.Add(para)
            para = New SqlParameter("@tongno", _docgia.TongNo)
            cm.Parameters.Add(para)
            para = New SqlParameter("@maloaidocgia", _docgia.MaLoaiDocGia)
            cm.Parameters.Add(para)
            para = New SqlParameter("@manhanvien", _docgia.MaNhanVien)
            cm.Parameters.Add(para)

            Dim parama As New SqlParameter("@ma", 0)
            parama.Direction = ParameterDirection.Output
            cm.Parameters.Add(parama)
            Dim n As Integer = cm.ExecuteNonQuery()
            _docgia.Ma = cm.Parameters("@ma").Value
            cn.Close()
            Return n
        End Using
    End Function

    ' Lấy tên độc giả khi biết ma:
    Public Function LayTenDocGiaKhiBietMa(ByVal madg As Integer) As String
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As New SqlCommand("usp_LayTenDocGiaKhiBietMa", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim para As New SqlParameter("@madocgia", madg)
            cm.Parameters.Add(para)

            Dim str As String = cm.ExecuteScalar()
            cn.Close()
            Return str
        End Using
    End Function


    Public Function LayDocgiaKhiBietMaCoTheHetHan(ByVal _ma As Integer) As CDocGiaDTO
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As New SqlCommand("uspLayThongTinDocGiaKhiBietMaCoTheHetHan", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim para As New SqlParameter("@ma", _ma)
            cm.Parameters.Add(para)
            Using reader As IDataReader = cm.ExecuteReader(CommandBehavior.CloseConnection)
                If reader.Read() Then
                    Return New CDocGiaDTO(reader)
                End If
                Return Nothing
            End Using
        End Using
    End Function

    Public Function LayThongTinDocGiaKhiBietMa(ByVal _ma As Integer) As CDocGiaDTO
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As New SqlCommand("uspLayThongTinDocGiaKhiBietMa", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim para As New SqlParameter("@ma", _ma)
            cm.Parameters.Add(para)
            Using reader As IDataReader = cm.ExecuteReader(CommandBehavior.CloseConnection)
                If reader.Read() Then
                    Return New CDocGiaDTO(reader)
                End If
                Return Nothing
            End Using
        End Using
    End Function

    Public Function CapNhatDocGia(ByVal _docgia As CDocGiaDTO) As Integer
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As New SqlCommand("uspCapNhatDocGia", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim para As New SqlParameter("@ma", _docgia.Ma)
            cm.Parameters.Add(para)
            para = New SqlParameter("@hoten", _docgia.HoTen)
            cm.Parameters.Add(para)
            para = New SqlParameter("@diachi", _docgia.DiaChi)
            cm.Parameters.Add(para)
            para = New SqlParameter("@ngaysinh", _docgia.NgaySinh)
            cm.Parameters.Add(para)
            para = New SqlParameter("@email", _docgia.Email)
            cm.Parameters.Add(para)
            para = New SqlParameter("@maloaidocgia", _docgia.MaLoaiDocGia)
            cm.Parameters.Add(para)
            Dim n As Integer = cm.ExecuteScalar()
            cn.Close()
            Return n
        End Using
    End Function

    Public Function CapNhatNgayHetHan(ByVal _docgia As CDocGiaDTO) As Integer
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As New SqlCommand("uspCapNhatNgayHetHan", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim para As New SqlParameter("@ma", _docgia.Ma)
            cm.Parameters.Add(para)
            para = New SqlParameter("@ngayhethan", _docgia.NgayHetHan)
            cm.Parameters.Add(para)
            Dim n As Integer = cm.ExecuteScalar()
            cn.Close()
            Return n
        End Using
    End Function

    Public Function DanhSachDocGiaNoTienPhat() As List(Of CDocGiaDTO)
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As New SqlCommand("uspDanhSachDocGiaNoTienPhat", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim ldocgia As New List(Of CDocGiaDTO)
            Using reader As IDataReader = cm.ExecuteReader(CommandBehavior.CloseConnection)
                While reader.Read()
                    ldocgia.Add(New CDocGiaDTO(reader))
                End While
            End Using
            Return ldocgia
        End Using
    End Function

    ' Tra cứu độc giả khi biết mã:
    Public Function TraCuu(ByVal madocgia As Integer) As CDocGiaDTO
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As New SqlCommand("usp_TraCuuDocGia", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim para As New SqlParameter("@Ma", madocgia)
            cm.Parameters.Add(para)
            Dim docgiadto As CDocGiaDTO
            Dim reader As IDataReader = cm.ExecuteReader()
            If reader.Read() Then
                docgiadto = New CDocGiaDTO(reader)
            Else
                docgiadto = Nothing
            End If
            Return docgiadto
        End Using
    End Function


    Public Function TraCuuKhongCoMaDocGia(ByVal docgiacrt As CDocGiaCrt) As List(Of CDocGiaDTO)
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim sKTTenDocGia As String = " 1=1 "
            Dim strKTEmail As String = " 1=1 "
            Dim strKTNo As String = " 1=1 "
            Dim sKTTenNhanVienLapThe As String = " 1=1 "
            Dim sKTTenLoaiDocGia As String = " 1=1 "
            Dim sKTNgayLapThe As String = " 1=1 "
            Dim sKTNgayHetHan As String = " 1=1 "
            Dim sKTNgaySinhTu As String = " 1=1 "
            Dim sKTNgaySinhDen As String = " 1=1 "
            Dim sKTDiaChi As String = " 1=1 "


            Dim cm As SqlCommand


            If (docgiacrt.CheckTenDocGia = True) Then
                sKTTenDocGia = "dg.HoTen like N'%" + docgiacrt.TenDocGia + "%'"
            End If


            If (docgiacrt.CheckDiaChi = True) Then
                sKTDiaChi = " dg.Diachi like N'%" + docgiacrt.DiaChi + "%'"
            End If

            If (docgiacrt.CheckEmail = True) Then
                strKTEmail = " dg.Email like N'%" + docgiacrt.Email + "%'"
            End If

            If (docgiacrt.CheckNgaySinhTu = True) Then
                sKTNgaySinhTu = " datepart(Year,dg.NgaySinh) between " + docgiacrt.NgaySinh.Year.ToString() + " and " + docgiacrt.NgaySinhDen.Year.ToString()
            End If


            If (docgiacrt.CheckNgayLapThe = True) Then
                sKTNgayLapThe = " dg.NgayLapThe between '" + docgiacrt.NgayLapTheTu.Date + "' and '" + docgiacrt.NgayLapTheDen.Date + "'"
            End If

            If (docgiacrt.CheckNgayHetHan = True) Then
                sKTNgayHetHan = " dg.NgayHetHan between '" + docgiacrt.NgayHetHanTu.Date + "' and '" + docgiacrt.NgayHetHanDen.Date + "'"
            End If

            If (docgiacrt.CheckNo = True) Then
                strKTNo = " dg.TongNo = " + docgiacrt.TongNo.ToString()
            End If

            If (docgiacrt.CheckNhanVienLapThe = True) Then
                sKTTenNhanVienLapThe = " nv.HoTen like N'%" + docgiacrt.TenNhanVienLapThe + "%'"
            End If


            If (docgiacrt.CheckLoaiDocGia) Then
                sKTTenLoaiDocGia = " ldg.Ten like N'%" + docgiacrt.TenLoaiDocGia + "%'"
            End If


            Dim sql = String.Format("select dg.Ma, dg.HoTen, dg.NgaySinh, dg.DiaChi, dg.Email,dg.MaLoaiDocGia, ldg.Ten as TenLoaiDocGia, dg.NgayLapThe,dg.NgayHetHan,dg.TongNo, dg.MaNhanVien as MaNhanVien, nv.HoTen as TenNhanVien from DocGia dg,NhanVien nv, LoaiDocGia ldg where {0} and {1} and {2} and  {3} and {4} and {5} and {6} and {7} and dg.MaNhanVien = nv.Ma and {8} and dg.MaLoaiDocGia = ldg.Ma", sKTTenDocGia, sKTDiaChi, strKTEmail, sKTNgaySinhTu, sKTNgayLapThe, sKTNgayHetHan, strKTNo, sKTTenNhanVienLapThe, sKTTenLoaiDocGia)
            cm = New SqlCommand(sql, cn)

            Dim listdocgiadto As New List(Of CDocGiaDTO)

            Using reader As IDataReader = cm.ExecuteReader(CommandBehavior.CloseConnection)
                While reader.Read()
                    listdocgiadto.Add(New CDocGiaDTO(reader))
                End While
            End Using
            Return listdocgiadto
        End Using
    End Function



End Class
